﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[fnMSX_get_environment_type]
#-- Purpose:		Look at the server name to look for specific nomenclatures
#--					that define what environment a server is part of
#--					(Test, QA, or Production)
#--	Last Update:	02/13/2017
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE FUNCTION [dbo].[fnMSX_get_environment_type]
(
	@server_name		varchar(60),
	@instance_name		varchar(60),
	@is_msx_server		bit				= 0
)
RETURNS nvarchar(10)
AS
BEGIN
	--- Declare Local Variables
	DECLARE @environment_type nvarchar(10)

	--- Retrieve the environment type for the requested server
	;WITH srv AS (
		SELECT		(CASE WHEN ssd.[ssd_is_disabled] = 1 THEN 'Disabled' ELSE ISNULL(cfe.[cfe_environment_name], 'Production') END) [cfe_environment_name],
					ROW_NUMBER() OVER (
							PARTITION BY ssd.[ssd_id] 
							ORDER BY CAST(CASE
								WHEN cfe.[cfe_begins_with] = 1 AND ssd.[ssd_instance_name] LIKE (cfe.[cfe_find_string] + '%') THEN 0
								WHEN cfe.[cfe_ends_with] = 1 AND ssd.[ssd_instance_name] LIKE (cfe.[cfe_find_string] + '%') THEN 0
								WHEN cfe.[cfe_begins_with] = 0 AND cfe.[cfe_ends_with] = 0 AND ssd.[ssd_instance_name] LIKE ('%' + cfe.[cfe_find_string] + '%') THEN 0
								ELSE 1
								END as bit)) [rownum],
					ssd.*
		FROM		[dbo].[tblMSX_server_discovery] ssd
		LEFT JOIN	[dbo].[tblMSX_configuration_environment] cfe ON (
						cfe.[cfe_msx] = 1
						AND ssd.[ssd_is_msx_server] = 1
					)

					OR (
						ssd.[ssd_is_msx_server] = 0
						AND cfe.[cfe_msx] = 0
						AND (
							(
								cfe.[cfe_begins_with] = 1
								AND (
									ssd.[ssd_instance_name] LIKE (cfe.[cfe_find_string] + '%')
									OR (
										NOT ISNULL(ssd.[ssd_instance_name], SPACE(0)) LIKE (cfe.[cfe_find_string] + '%')
										AND ssd.[ssd_server_name] LIKE (cfe.[cfe_find_string] + '%')
									)
								)
							)
							OR (
								cfe.[cfe_ends_with] = 1
								AND (
									ssd.[ssd_instance_name] LIKE ('%' + cfe.[cfe_find_string])
									OR (
										NOT ISNULL(ssd.[ssd_instance_name], SPACE(0)) LIKE ('%' + cfe.[cfe_find_string])
										AND ssd.[ssd_server_name] LIKE ('%' + cfe.[cfe_find_string])
									)
								)
							)
							OR (
								cfe.[cfe_begins_with] = 0
								AND cfe.[cfe_ends_with] = 0
								AND (
									ssd.[ssd_instance_name] LIKE ('%' + cfe.[cfe_find_string] + '%')
									OR (
										NOT ISNULL(ssd.[ssd_instance_name], SPACE(0)) LIKE ('%' + cfe.[cfe_find_string] + '%')
										AND ssd.[ssd_server_name] LIKE ('%' + cfe.[cfe_find_string] + '%')
									)
								)
							)
						)
					)
		WHERE		ssd.[ssd_is_disabled] = 0
					AND [ssd_server_name] = @server_name
					AND ISNULL([ssd_instance_name], SPACE(0)) = ISNULL(@instance_name, SPACE(0))
					AND [ssd_is_msx_server] = @is_msx_server
	)
	SELECT		@environment_type = srv.[cfe_environment_name]
	FROM		srv
	WHERE		[rownum] = 1

	--- Return the environment type
	RETURN		ISNULL(@environment_type, 'Production')
END
